﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using WMSSyncData.Model.WMSModel;
using WMSSyncData.Utils;

namespace WMSSyncData.DAL.Oracle
{
    public class WMS_M_InStockRMDAL : BaseOracleDAL<WMS_T_InStockRM>
    {
        public List<WMS_T_InStockRM> GetSyncData(string sns)
        {
            var sql = @"select 'A0201'||to_char(sysdate, 'yyMMdd')||LPAD (SEQ_WMS_M_INSTOCKRM.NEXTVAL,5,'0') AS BillNo,temp.* from (
                        select DISTINCT t$orno AS RelatedBill,CASE 
	                    WHEN t$ITTP='收货' THEN
	                    '采购入库'
                        END as BillType,t$otbp AS Supplier,decode(t$cwar,'RMD3X','RMWHS','RMD4X','RMWHS',t$cwar) AS Storage_SN,t$cwar AS Ext3,0 AS Status,
                        'admin' as Creator,SYSDATE AS CreateTime,t$NAMA AS SupplierName  from  TTDDEV101101 where t$ITTP<>'发料' 
                    and t$host='1' and t$fire<>1 and t$cwar not in(" + sns+ ") " +
                    "and substr(t$orno,0,3)!='HFB' and substr(t$orno,0,3)!='HPR' and substr(t$orno,0,3)!='HUS' and substr(t$orno,0,3)!='HFR' and substr(t$orno,0,3)!='QUS') temp";
            var dt = OracleDBHelper.GetDataSet(sql).Tables[0];
            var result = MakeTablePackage(dt);

            return result;
        }

        /// <summary>
        /// 获取原料201的入库信息
        /// </summary>
        /// <param name="sns"></param>
        /// <returns></returns>
        public DataTable GetSyncRMData(string sns)
        {
//            var sql = @"SELECT rawtohex(sys_guid()) as GUID,'A0101'||to_char(sysdate, 'yyMMdd')||LPAD (SEQ_WMS_M_Instock.NEXTVAL,5,'0') AS BillNo,temp.* FROM(
//SELECT  DISTINCT  t$orno as RelatedBill, '成品入库' as BillType,t$sqnb AS SN,t$item AS PartNumber,t$pono AS RowNo,t$qoor AS PlanQty,0 AS RealQty,0 AS status,t$cuqp AS Unit,'admin' as Creator,SYSDATE as createtime,nvl(t$cwar,' ') as Storage_SN FROM TTDDEV101101 WHERE  t$ITTP<>'发料' and t$host=1 and t$cwar in (" + sns + "))temp ";
            var sql = @"SELECT rawtohex(sys_guid()) as GUID,'A0101'||to_char(sysdate, 'yyMMdd')||LPAD (SEQ_WMS_M_Instock.NEXTVAL,5,'0') AS BillNo,temp.* FROM(
SELECT  t$orno as RelatedBill, '成品入库' as BillType,t$sqnb AS SN,t$item AS PartNumber,t$pono AS RowNo,t$qoor AS PlanQty,0 AS RealQty,0 AS status,t$cuqp AS Unit,'admin' as Creator,SYSDATE as createtime,nvl(t$cwar,' ') as Storage_SN FROM TTDDEV101101 WHERE  t$ITTP<>'发料' and t$host=1 and t$fire<>'1'  and t$cwar in (" + sns + "))temp ";
            var dt = OracleDBHelper.GetDataSet(sql).Tables[0];

            return dt;
        }

        /// <summary>
        /// 获取半成品领料的信息
        /// </summary>
        /// <returns></returns>
        public DataTable GetSyncSFPData()
        {
            var sql = @"SELECT  rawtohex(sys_guid()) as GUID, BillNO,RelatedbillNo as RelatedBill,RowNo, PartNumber,SequenceNo as SN,Creator as Creator,CreateTime as createtime,OutQty as Qty,nvl(OutWarehouse,' ') as Storage_SN, unit FROM WMS_M_OUTSTOCK WHERE BILLTYPE='半成品出库' and Data_Status='1'";
            var dt = OracleDBHelper.GetDataSet(sql).Tables[0];

            return dt;
        }
        /// <summary>
        /// 获取生产退料数据
        /// </summary>
        /// <returns></returns>
        public List<WMS_T_InStockRM> GetPRData()
        {

            var sql = @"SELECT DISTINCT BillNO,RelatedbillNo as RelatedBill,billtype,nvl(InWarehouse,' ') as Storage_SN,
0 AS status,Remark,creator as Creator,sysdate as createtime FROM WMS_M_InstockRM WHERE Data_Status=1 and billtype='退料入库'";
            var dt = OracleDBHelper.GetDataSet(sql).Tables[0];
            var result = MakeTablePackage(dt);

            return result;
        }

    }

    public class WMS_M_InStockRMRowsDAL : BaseOracleDAL<WMS_T_InStockRMRows>
    {
        public List<WMS_T_InStockRMRows> GetSyncData(string sns)
        {
            var sql = @"select  t$orno AS RelatedBill,t$item AS PartNumber,t$pono AS RowNo,t$sqnb AS OrderNo,t$qoor AS PlanQty,0 AS RealQty,t$cuqp AS Unit, 0 AS Status,'admin' as Creator,SYSDATE AS CreateTime,T$REVI AS Version  from  TTDDEV101101 where t$ITTP<>'发料' and t$host=1 and t$fire<>'1'  and t$cwar not in (" + sns+ ") and substr(t$orno,0,3)!='HFB' and substr(t$orno,0,3)!='HPR' and substr(t$orno,0,3)!='HUS' and substr(t$orno,0,3)!='HFR' ";

            var dt = OracleDBHelper.GetDataSet(sql).Tables[0];

            var result = MakeTablePackage(dt);
            return result;
        }

        public List<WMS_T_InStockRMRows> GetSyncDeleteData(string sns)
        {
            var sql = @"select  t$orno AS RelatedBill,t$item AS PartNumber,t$pono AS RowNo,t$sqnb AS OrderNo,t$qoor AS PlanQty,0 AS RealQty,t$cuqp AS Unit, 0 AS Status,'admin' as Creator,SYSDATE AS CreateTime,T$REVI AS Version  from  TTDDEV101101 where t$ITTP<>'发料' and t$host=1 and t$fire='1'  and t$cwar not in (" + sns + ") and rownum <= 3000 ";

            var dt = OracleDBHelper.GetDataSet(sql).Tables[0];

            var result = MakeTablePackage(dt);
            return result;
        }


        public int UpdateStatus(List<WMS_T_InStockRMRows> list)
        {
            StringBuilder sb = new StringBuilder();

            sb.AppendLine("begin");
            foreach (var item in list)
            {
                var sql = "UPDATE TTDDEV101101 set  t$host=2,t$wdte1=SYSDATE where t$orno='" + item.RelatedBill + "' and t$pono='" + item.RowNo + "' and t$sqnb='" + item.OrderNo + "';";
                sb.AppendLine(sql);
            }
            sb.AppendLine("end;");

            var resultSql = sb.ToString().Trim();
            var result = OracleDBHelper.ExecuteNonQuery(resultSql);
            return result;
        }

        public int UpdateDeleteRowStatus(List<WMS_T_InStockRMRows> dt)
        {
            StringBuilder sb = new StringBuilder();

            sb.AppendLine("begin");
            foreach (WMS_T_InStockRMRows item in dt)
            {
                var sql = "UPDATE TTDDEV101101 set  t$host=2,t$wdte1=SYSDATE where t$orno='" + item.RelatedBill + "' and t$pono='" + item.RowNo + "' and t$sqnb='" + item.OrderNo + "';";
                sb.AppendLine(sql);
            }
            sb.AppendLine("end;");

            var resultSql = sb.ToString().Trim();
            var result = OracleDBHelper.ExecuteNonQuery(resultSql);
            return result;
        }

        public int UpdateDeleteRowStatus(DataTable dt)
        {
            StringBuilder sb = new StringBuilder();

            sb.AppendLine("begin");
            foreach (DataRow item in dt.Rows)
            {
                var sql = "UPDATE TTDDEV101101 set  t$host=2,t$wdte1=SYSDATE where t$orno='" + item["RelatedBill"].ToString() + "' and t$pono='" + item["RowNo"].ToString() + "' and t$sqnb='" + item["OrderNo"].ToString() + "';";
                sb.AppendLine(sql);
            }
            sb.AppendLine("end;");

            var resultSql = sb.ToString().Trim();
            var result = OracleDBHelper.ExecuteNonQuery(resultSql);
            return result;
        }

        public int UpdateStatus(DataTable dt)
        {
            StringBuilder sb = new StringBuilder();

            sb.AppendLine("begin");
            foreach (DataRow dr in dt.Rows)
            {
                var sql = "UPDATE TTDDEV101101 set  t$host=2,t$wdte1=SYSDATE where t$orno='" + dr["RelatedBill"].ToString() + "' and t$pono='" + dr["RowNo"].ToString() + "' and t$sqnb='" + dr["SN"].ToString() + "';";
                sb.AppendLine(sql);
            }
            sb.AppendLine("end;");

            var resultSql = sb.ToString().Trim();
            var result = OracleDBHelper.ExecuteNonQuery(resultSql);
            return result;
        }

        public DataTable GetChangeDT()
        {
            var sql = @"SELECT   t$orno AS RelatedBill,t$sqnb as OrderNo,t$pono as RowNo,t$status AS status,t$item as ProductNumber,t$qoor as PlanQty,t$text as Text,t$cwar as Storage_SN  FROM TTDDEV101101 WHERE t$ITTP<>'发料' and t$host=2 and t$status IN(2,3) and substr(t$orno,0,3)!='HFB' and substr(t$orno,0,3)!='HPR' and substr(t$orno,0,3)!='HUS' and substr(t$orno,0,3)!='HFR' ";

            var dt = OracleDBHelper.GetDataSet(sql).Tables[0];

            return dt;

        }


        /// <summary>
        /// 获取生产退料数据
        /// </summary>
        /// <returns></returns>
        public List<WMS_T_InStockRMRows> GetPRData()
        {

            var sql = @"SELECT   BillNO,RelatedbillNo as RelatedBill,PartNumber,RowNo,SequenceNo as OrderNo,InQty as PlanQty,Unit,
                        0 AS status,creator as Creator,createtime as createtime,Ext1,Ext2,Ext3 as Version FROM WMS_M_InstockRM 
                        WHERE Data_Status=1 and billtype='退料入库'";
            var dt = OracleDBHelper.GetDataSet(sql).Tables[0];
            var result = MakeTablePackage(dt);

            return result;
        }

        public int UpdatePRStatus(List<WMS_T_InStockRMRows> list)
        {
            StringBuilder sb = new StringBuilder();

            sb.AppendLine("begin");
            foreach (var item in list)
            {
                var sql = "UPDATE WMS_M_InstockRM set  Data_Status=2,Data_ReadTime=SYSDATE where BillNo='" + item.BillNo + "' and RowNo='" + item.RowNo  + "';";
                sb.AppendLine(sql);
            }
            sb.AppendLine("end;");

            var resultSql = sb.ToString().Trim();
            var result = OracleDBHelper.ExecuteNonQuery(resultSql);
            return result;
        }



    }

}
